from typing import List

from sqlalchemy import BinaryExpression, func, select

from forward.api.admin.schemas.lucky.code_batch import CodeBatchDetailOutSchema
from forward.common.enums import CodeStatusEnum
from forward.dependencies.database import db
from forward.model.orm.base import BaseORM
from forward.model.schemas.lucky.code_batch import CodeBatchOutSchema
from forward.model.tables.activity import CodeBatchModel, CodeModel
from forward.utils.page_utils import PageSchema, PageUtils


class CodeBatchORM(BaseORM[CodeBatchOutSchema]):
    MODEL = CodeBatchModel
    OUT_SCHEMA = CodeBatchOutSchema

    async def lists(
        self,
        where: List[BinaryExpression] | None = None,
        page_num: int = 1,
        page_size: int = 20,
        **kwargs
    ) -> PageSchema[CodeBatchDetailOutSchema]:
        """
        码号批次列表
        """
        where_conditions = []
        for k, v in kwargs.items():
            if getattr(CodeBatchModel, k):
                where_conditions.append(getattr(CodeBatchModel, k) == v)
        if where:
            where_conditions.extend(where)
        # 子查询计算 put_in_num
        sub_code_put_in_num = (
            select(
                CodeModel.code_batch_id, func.count(CodeModel.id).label("put_in_num")
            )
            .group_by(CodeModel.code_batch_id)
            .subquery()
        )
        sub_code_unused_num = (
            select(
                CodeModel.code_batch_id, func.count(CodeModel.id).label("unused_num")
            )
            .where(CodeModel.status == CodeStatusEnum.UNUSED.value)
            .group_by(CodeModel.code_batch_id)
            .subquery()
        )
        statement = (
            select(
                CodeBatchModel,
                sub_code_put_in_num.c.put_in_num,
                sub_code_unused_num.c.unused_num,
            )
            .select_from(CodeBatchModel)
            .outerjoin(
                sub_code_put_in_num,
                sub_code_put_in_num.c.code_batch_id == CodeBatchModel.id,
            )
            .outerjoin(
                sub_code_unused_num,
                sub_code_unused_num.c.code_batch_id == CodeBatchModel.id,
            )
            .where(*where_conditions)
            .order_by(CodeBatchModel.id.desc())
        )
        pagination = await PageUtils.paginate(
            db=db,
            query=statement,
            page_num=page_num,
            page_size=page_size,
            schema=CodeBatchDetailOutSchema,
        )
        return pagination
